﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace NG
{
    class subIndustryHandler:IsubIndustryHandler
    {
        private string tableName = "[dbo].[SubIndustries]";

        public void addSubIndustry(SubIndustry subIndustry)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "INSERT INTO" + tbn + "(industry,subIndustryNo,subIndustryName,subIndustryDesc,subIndustryExtCode) VALUES("
             + subIndustry.industry + "," + subIndustry.subIndustry + ",'"+ subIndustry.subIndustryName+"','" + subIndustry.subIndustyDesc + "','" + subIndustry.subIndustyExtCode + "');";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't discon`nect from the Database");
            }
        }

        public void updateSubIndustry(SubIndustry subIndustry,SubIndustry newSubIndustry)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "UPDATE" + tbn + " SET industry=" + newSubIndustry.industry +
                ",subIndustryName = '" + newSubIndustry.subIndustryName + "'," + "subIndustryDesc= '" + newSubIndustry.subIndustyDesc + "'," +
                "subIndustryExtCode = '"+ newSubIndustry.subIndustyExtCode + "'"
                + "WHERE (industry = " + subIndustry.industry  +"AND subIndustryNo = " + subIndustry.subIndustry + ");";
            SqlCommand cmd = new SqlCommand(query, con.connection);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }




        public LinkedList<SubIndustry> allSubIndustriesByIndustry(Industry indust)
        {

            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<SubIndustry> SubIndustryList = new LinkedList<SubIndustry>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "SELECT * FROM " + tbn+" WHERE industry = " + indust.industryNum ;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    SubIndustry SubIndustry = new SubIndustry(int.Parse(rdr["industry"].ToString()),
                       int.Parse(rdr["subIndustryNo"].ToString()), rdr["SubIndustryName"].ToString(), rdr["SubIndustryDesc"].ToString(),
                       rdr["SubIndustryExtCode"].ToString());

                    SubIndustryList.AddLast(SubIndustry);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return SubIndustryList;

            
        }

        public LinkedList<SubIndustry> allSubIndustriesByIndustry()
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<SubIndustry> SubIndustryList = new LinkedList<SubIndustry>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "SELECT * FROM"+ tbn;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    SubIndustry SubIndustry = new SubIndustry(int.Parse(rdr["industry"].ToString()),
                       int.Parse(rdr["subIndustryNo"].ToString()), rdr["SubIndustryName"].ToString(), rdr["SubIndustryDesc"].ToString(),
                       rdr["SubIndustryExtCode"].ToString());

                    SubIndustryList.AddLast(SubIndustry);


                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return SubIndustryList;
        }
    }
}
